<?php
/**
 * @ Author: anchen
 * @ Date:   2016-04-29 18:27:31
 * @ Last Modified by:   anchen
 * @ Last Modified time: 2016-04-29 21:09:49
 * @ csv文件的游戏道具表导入转成数组格式
 */
header("Content-type:text/html;charset=utf-8");

//生成SQL文件
$str = "/*
Navicat MySQL Data Transfer

Source Server         : localhost_3306
Source Server Version : 50620
Source Host           : 127.0.0.1:3306
Source Database       : fanren_sy_log_1_0_1_1_a1

Target Server Type    : MYSQL
Target Server Version : 50620
File Encoding         : 65001

Date: 2017-08-09 11:33:42
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `test_order_record`
-- ----------------------------
DROP TABLE IF EXISTS `test_order_record`;
CREATE TABLE `test_order_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '编号ID',
  `order_id` varchar(64) NOT NULL DEFAULT '' COMMENT '订单号',
  `status` int(11) NOT NULL DEFAULT '0' COMMENT '状态：1-未支付,2-支付成功',
  `create_dt` datetime NOT NULL DEFAULT '2010-01-01 00:00:00' COMMENT '创建时间',
  `username` varchar(60) NOT NULL DEFAULT '' COMMENT '玩家账号',
  `player_id` int(11) NOT NULL DEFAULT '0' COMMENT 'player id',
  `money` int(11) NOT NULL DEFAULT '0' COMMENT '充值人民币金额',
  `gold` int(11) NOT NULL DEFAULT '0' COMMENT '充值应获得的元宝',
  `ip` varchar(64) NOT NULL DEFAULT '' COMMENT 'player ip',
  `uuid` varchar(64) NOT NULL DEFAULT '' COMMENT 'uuid',
  `plaform_order_id` varchar(64) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `order_id_key` (`order_id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test_order_record
-- ----------------------------
";
file_put_contents('./xls/test_order_record.sql', $str);

//打开csv文件
$handle = fopen("./xls/wx360chargedatas.csv","r");
//调用函数
$array = write_in_file($handle);

echo '<br/>程序执行结束!';
exit;

//定义函数 write_in_file()
function write_in_file($handle)
{
    $out = array ();
    $n = 0;
    while ($csvData = fgetcsv($handle, 10000))
    {
        //排除不需要的行
        if($n==0||$n==1)
        {
            $n++;
            continue;
        }
        //拼接sql语句
        $insertSql = "INSERT INTO `test_order_record` (`order_id`,`status`,`create_dt`,`username`,`player_id`,`money`,`gold`,`plaform_order_id`) VALUES (";
        //获取需要的列
        foreach($csvData as $k=>$v){
            switch($k){
                case 1:$insertSql .= "'".$v."',2,";//成功订单号+订单状态    order_id,status
                    break;
                case 3:$insertSql .= "'".$v.":00',";//订单生成时间    create_dt
                    break;
                case 4:$insertSql .= "'".$v."',";//username
                    break;
                case 5:$insertSql .= "'".$v."',";//player_id
                    break;
                case 6:$insertSql .= "'".$v."',";//money
                    break;
                case 7:$insertSql .= "'".$v."',";//gold
                    break;
                case 10:$insertSql .= "'".$v."'";//最后一个不带逗号    platform_order_id
                default:break;//其他不拼接
            }
            /*if($k==0){
                $insertSql .= "'".$v."',";//角色ID          player_id
            }else if($k==2){
                $insertSql .= "'".$v."',";//账号ID          username
            }else if($k==3){
                $insertSql .= "'".$v."',2,";//成功订单号+订单状态       order_id,status
            }else if($k==4){
                $insertSql .= "'".$v."',";//元宝            gold
            }else if($k==5){
                $insertSql .= "'".$v."',";//充值金额        money
            }else if($k==6){
                $insertSql .= "'".$v."',";//订单生成时间    create_dt
            }else if($k==7){
                $insertSql .= "'".$v."'";//渠道订单号      plaform_order_id
            }*/
        }
        $insertSql .= ");";//一条SQL语句拼接结束

        //SQL语句写入sql文件
        file_put_contents('./xls/test_order_record.sql', $insertSql."\r\n", FILE_APPEND);
    }
}
?>